Use OFD
GO
/*Bouquet*/
CREATE PROC getBoutquetByHome
AS
	SELECT * FROM Bouquet WHERE B_Home=1
GO
CREATE PROC getAllBouquet
AS
	SELECT * FROM Bouquet
GO
CREATE PROC getBouquetByID
	@B_ID nvarchar(50)
AS
	SELECT * FROM Bouquet
	WHERE B_ID=@B_ID
GO
CREATE PROC UpdateStatusBouquet
	@B_ID nvarchar(50)
AS
	UPDATE Bouquet SET B_Status=0
	WHERE B_ID=@B_ID
GO
CREATE PROC InsertBouquet
	@B_ID nvarchar(50),
	@B_Name nvarchar(200),
	@B_Price float,
	@B_Details nvarchar(MAX),
	@B_Image nvarchar(MAX),
	@C_ID int
AS
INSERT INTO Bouquet VALUES(@B_ID,@B_Name,@B_Price,@B_Details,@B_Image,@C_ID,0,1)
GO
CREATE PROC UpdateBouquet
	@B_ID nvarchar(50),
	@B_Name nvarchar(200),
	@B_Price float,
	@B_Details nvarchar(MAX),
	@B_Image nvarchar(MAX),
	@C_ID int,
	@B_Home int
AS
UPDATE Bouquet SET B_Name=@B_Name,B_Price=@B_Price,B_Details=@B_Details,B_Image=@B_Image,C_ID=@C_ID,B_Home=@B_Home
WHERE B_ID=@B_ID
/*Messages*/
CREATE PROC getAllMessages
AS
	SELECT * FROM Messages
GO
CREATE PROC insertMessages
	@Mes_Details nvarchar(MAX)
AS
	INSERT INTO Messages VALUES(@Mes_Details)
GO
CREATE PROC delMessages
	@Mes_ID int
AS
	DELETE FROM Messages WHERE Mes_ID=@Mes_ID
GO	
CREATE PROC updateMessages
	@Mes_ID int,
	@Mes_Details nvarchar(MAX)
AS
	UPDATE Messages SET Mes_Details=@Mes_Details
	WHERE Mes_ID=@Mes_ID
GO
/*Bill*/
DROP PROC getAllBill
CREATE PROC getAllBill
AS
	SELECT * FROM Bill Order by Bi_ID DESC
GO
CREATE PROC getBillById
	@Bi_ID int
AS
	SELECT * FROM Bill 
	WHERE Bi_ID=@Bi_ID
GO

CREATE PROC updateBillStatus
	@Bi_ID int,
	@Bi_Status nvarchar(50)
AS
	UPDATE Bill SET Bi_Status=@Bi_Status
	WHERE Bi_ID=@Bi_ID
GO
DROP PROC getBillByDoneStatus
CREATE PROC getBillByDoneStatus
	@Bi_DateCompleted nvarchar(200)
AS
	SELECT Count(*)as Number, Sum(Bi_TotalAmount) as Total FROM Bill
	WHERE Bi_DateCompleted LIKE @Bi_DateCompleted+'%' AND Bi_Status LIKE 'Has delivered'
GO
DROP PROC getBillByNotStatus
CREATE PROC getBillByNotStatus
	@Bi_DateCompleted nvarchar(200)
AS
	SELECT Count(*)as Number FROM Bill
	WHERE Bi_DateCompleted LIKE @Bi_DateCompleted+'%' AND Bi_Status LIKE 'No delivered'
GO	
CREATE PROC getBillByDateAndStatus
	@Bi_DateCompleted nvarchar(200)
AS
	SELECT * FROM Bill
	WHERE Bi_DateCompleted LIKE @Bi_DateCompleted+'%' AND Bi_Status LIKE 'Has delivered'
GO
CREATE PROC InsertBill
	@Bi_TotalAmount float,
	@Bi_Address nvarchar(MAX),
	@Bi_Date nvarchar(200),
	@Bi_DateCompleted nvarchar(200),
	@M_ID nvarchar(50),
	@Bi_Messages nvarchar(MAX),
	@Bi_Status nvarchar(50)
AS
INSERT INTO Bill VALUES(@Bi_TotalAmount,@Bi_Address,@Bi_Date,@Bi_DateCompleted,@M_ID,@Bi_Messages,@Bi_Status)
GO
DROP PROC getBillByManyFiels
CREATE PROC getBillByManyFiels
	@Bi_TotalAmount float,
	@Bi_Date nvarchar(200),
	@Bi_DateCompleted nvarchar(200),
	@Bi_Address nvarchar(MAX),
	@M_ID nvarchar(50)
AS
	SELECT * FROM Bill 
	WHERE Bi_TotalAmount=@Bi_TotalAmount AND Bi_Date=@Bi_Date AND Bi_DateCompleted=@Bi_DateCompleted
	AND Bi_Address=@Bi_Address AND M_ID=@M_ID
GO
SELECT * FROM Bill WHERE 
/*BillDetails*/ 
CREATE PROC getBillDetailsByID 
	@Bi_ID int
AS
	SELECT * FROM BillDetails
	WHERE Bi_ID=@Bi_ID
GO
CREATE PROC InsertBillDetails
	@Bi_ID int,
	@B_ID nvarchar(50),
	@BiD_Number int,
	@BiD_Amount float
AS
INSERT INTO BillDetails VALUES(@Bi_ID,@B_ID,@BiD_Number,@BiD_Amount)
/*Catalog*/
CREATE PROC getAllCatalog
AS
	SELECT * FROM Catalog
GO
CREATE PROC getCatalogByID
	@C_ID int
AS
	SELECT * FROM Catalog
	WHERE C_ID=@C_ID
GO
CREATE PROC updateCatalog
	@C_ID int,
	@C_Name nvarchar(50)
AS
	UPDATE Catalog SET C_Name=@C_Name
	WHERE C_ID=@C_ID
GO
CREATE PROC updateCatalogStatus
	@C_ID int
AS
	UPDATE Catalog SET C_Status=0
	WHERE C_ID=@C_ID
Go
CREATE PROC insertCatalog
	@C_Name nvarchar(50)
AS
	INSERT INTO Catalog VALUES(@C_Name,1)
GO
/*Member*/
CREATE PROC updatePass
	@M_ID nvarchar(50),
	@M_Password nvarchar(200)
AS
	UPDATE Member SET M_Password=@M_Password
	WHERE M_ID=@M_ID
GO
CREATE PROC gettAllMember
AS
	SELECT * FROM Member
GO
CREATE PROC updateMemberStatus
	@M_ID nvarchar(50)
AS
	UPDATE Member SET M_Status=0
	WHERE M_ID=@M_ID
GO
CREATE PROC validLoginMember
	@M_UserName nvarchar(200),
	@M_Password nvarchar(200)
AS
	SELECT * FROM Member
	WHERE M_UserName=@M_UserName AND M_Password=@M_Password AND M_Status=1
GO
CREATE PROC getMemberByEmail
	@M_Mail nvarchar(50)
AS
	SELECT * FROM Member
	WHERE M_Mail=@M_Mail
GO
CREATE PROC ForgetPassword
	@M_Mail nvarchar(50)
AS
	UPDATE Member SET M_Password='123456'
	WHERE M_Mail=@M_Mail
GO
CREATE PROC InsertMember
	@M_ID nvarchar(50),
	@M_FullName nvarchar(50),
	@M_Bob datetime,
	@M_Gender nvarchar(50),
	@M_Phone nvarchar(50),
	@M_Address nvarchar(MAX),
	@M_Mail nvarchar(50),
	@M_UserName nvarchar(200),
	@M_Password nvarchar(200)
AS
	INSERT INTO Member VALUES(@M_ID,@M_FullName,@M_Bob,@M_Gender,@M_Phone,@M_Address,@M_Mail,@M_UserName,@M_Password,1)
GO
CREATE PROC checkID
	@M_ID nvarchar(50)
AS
	SELECT * FROM Member WHERE M_ID=@M_ID
GO
CREATE PROC checkUser
	@M_UserName nvarchar(200)
AS
	SELECT * FROM Member WHERE M_UserName=@M_UserName
GO
CREATE PROC updateMember
	@M_ID nvarchar(50),
	@M_FullName nvarchar(50),
	@M_Bob datetime,
	@M_Gender nvarchar(50),
	@M_Phone nvarchar(50),
	@M_Address nvarchar(MAX),
	@M_Mail nvarchar(50)
AS
	UPDATE Member SET M_FullName=@M_FullName,M_Bob=@M_Bob,M_Gender=@M_Gender,M_Phone=@M_Phone,M_Address=@M_Address,M_Mail=@M_Mail
	WHERE M_ID=@M_ID
GO
/*Admin*/
CREATE PROC validLoginAdmin
	@UserName nvarchar(50),
	@Password nvarchar(50)
AS 
	SELECT * FROM Admin 
	WHERE UserName=@UserName AND Password=@Password
GO

CREATE PROC updateAdmin
	@ID int,
	@Password nvarchar(50)
AS 
UPDATE Admin SET Password=@Password
WHERE ID=@ID
GO
	
/*GiftCatalog*/
CREATE PROC getAllGiftCatalog
AS
	SELECT * FROM GiftCatalog
GO
CREATE PROC getCatalogGiftByID
	@CG_ID int
AS
	SELECT * FROM GiftCatalog 
	WHERE CG_ID=@CG_ID
GO
CREATE PROC updateGiftCatalog
	@CG_ID int,
	@CG_Name nvarchar(50)
AS
	UPDATE GiftCatalog SET CG_Name=@CG_Name
	WHERE CG_ID=@CG_ID
GO
CREATE PROC updateGiftCatalogStatus
	@CG_ID int
AS
	UPDATE GiftCatalog SET CG_Status=0
	WHERE CG_ID=@CG_ID
Go
CREATE PROC insertGiftCatalog
	@CG_Name nvarchar(50)
AS
	INSERT INTO GiftCatalog VALUES(@CG_Name,1)
GO
/*Gift*/
CREATE PROC getAllGift
AS
	SELECT * FROM Gift
GO
DROP PROC getGiftByCID
CREATE PROC getGiftByCID
	@CG_ID int
AS
	SELECT * FROM Gift WHERE CG_ID=@CG_ID
GO
CREATE PROC getGiftByID
	@G_ID nvarchar(50)
AS
	SELECT * FROM Gift WHERE G_ID=@G_ID
GO
CREATE PROC insertGift
	@G_ID nvarchar(50),
	@G_Name nvarchar(200),
	@G_Price float,
	@G_Details nvarchar(MAX),
	@G_Image nvarchar(MAX),
	@CG_ID int
AS
	INSERT INTO Gift VALUES(@G_ID,@G_Name,@G_Price,@G_Details,@G_Image,@CG_ID,1)
GO
CREATE PROC updateStatusGift
	@G_ID nvarchar(50)
AS
	UPDATE Gift SET G_Status=0
	WHERE G_ID=@G_ID
GO
CREATE PROC updateGift
	@G_ID nvarchar(50),
	@G_Name nvarchar(200),
	@G_Price float,
	@G_Details nvarchar(MAX),
	@G_Image nvarchar(MAX),
	@CG_ID int
AS
	UPDATE Gift SET G_Name=@G_Name,G_Price=@G_Price,G_Details=@G_Details,G_Image=@G_Image,CG_ID=@CG_ID
	WHERE G_ID=@G_ID
GO
/*Bill Gift*/
CREATE PROC InsertBillGift
	@Bi_ID int,
	@G_ID nvarchar(50),
	@BG_Quantity int,
	@BG_Amount float
AS
INSERT INTO BillGift VALUES(@Bi_ID,@G_ID,@BG_Quantity,@BG_Amount)
GO
DROP PROC returnBillGiftByBiID
CREATE PROC returnBillGiftByBiID
	@Bi_ID int
AS 
	SELECT * FROM BillGift
	WHERE Bi_ID=@Bi_ID
GO
/*Comment*/
CREATE PROC getAllComment
AS
	SELECT * FROM Comment
GO
CREATE PROC getCommentById
	@Comment_ID int
AS
	SELECT * FROM Comment
	WHERE Comment_ID=@Comment_ID
GO
DROP PROC getCommentByMail
CREATE PROC getCommentByMail
	@Comment_Mail nvarchar(MAX)
AS
	SELECT * FROM Comment
	WHERE Comment_Mail LIKE @Comment_Mail+'%'
GO
CREATE PROC insertComment
	@Comment_Mail nvarchar(MAX),
	@Comment_Title nvarchar(MAX),
	@Comment_Content nvarchar(MAX)
AS
	INSERT INTO Comment VALUES(@Comment_Mail,@Comment_Title,@Comment_Content)
GO
CREATE PROC delComment
	@Comment_ID int
AS
	DELETE FROM Comment
	WHERE Comment_ID=@Comment_ID
GO